const express = require("express");
const multer = require("multer");
const request = require("request");
const mysql = require("mysql");
const path = require("path");
const ffmpeg = require("fluent-ffmpeg");
var pool = mysql.createPool({
  connectionLimit: 10,
  host: "127.0.0.1",
  user: "root", //数据库用户名
  password: "123123", //数据库密码
  database: "test",
});

const https = require("https");
const fs = require("fs");
// const options = {
//   key: fs.readFileSync("./key.pem"),
//   cert: fs.readFileSync("./certificate.pem"),
// };

const app = express();
const upload = multer({
  storage: multer.diskStorage({
    // 设置上传文件的存储路径和文件名
    destination: "uploads/",
    filename: (req, file, cb) => {
      const extname = path.extname(file.originalname);
      const basename = path.basename(file.originalname, extname);
      cb(null, basename + "-" + Date.now() + extname);
    },
  }),
});

// 微信小程序登录接口
app.get("/login", (req, res) => {
  // 小程序的AppID和AppSecret
  const appId = "wx0f8f26e447003a8b";
  const appSecret = "2b8fbaf3fdc5f23d31968a22018c141d";
  // 获取客户端传递的code参数
  const code = req.query.code;

  // 根据code向微信服务器发送请求，获取用户的session_key和openid
  const url = `https://api.weixin.qq.com/sns/jscode2session?appid=${appId}&secret=${appSecret}&js_code=${code}&grant_type=authorization_code`;
  // 发起请求
  request(url, (error, response, body) => {
    if (!error && response.statusCode === 200) {
      const data = JSON.parse(body);
      const sessionKey = data.session_key;
      const openid = data.openid;

      // 在这里可以根据需要进行进一步处理，比如保存用户信息等

      // 返回session_key和openid给前端
      res.json({
        sessionKey,
        openid,
      });
    } else {
      // 处理请求错误
      res.status(response.statusCode).json({
        error: error || response.statusCode,
      });
    }
  });
});
// 查询审核的图片
app.post("/examine", (req, res) => {
  let openid = req.query.openid;
  let state = req.query.state;
  let sql = `SELECT * FROM cs WHERE openid = '${openid}' and state=${state};`;
  pool.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query(sql, function (error, results, fields) {
      connection.release();
      console.dir(req);
      res.header("Access-Control-Allow-Origin", "*");
      res.header("Access-Control-Allow-Headers", "content-type");
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options") res.send(200);
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 获取用户信息，并保存到数据库
app.post("/getUser", (req, res) => {
  let openid = req.query.openid;
  let nickname = req.query.nickname;
  let user_img = req.query.user_img;
  let zctime = req.query.zctime;
  let sql = `INSERT INTO user (openid, nickname, user_img,zctime) VALUES ('${openid}', '${nickname}', '${user_img}','${zctime}');`;
  pool.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query(sql, function (error, results, fields) {
      connection.release();
      console.dir(req);
      res.header("Access-Control-Allow-Origin", "*");
      res.header("Access-Control-Allow-Headers", "content-type");
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options") res.send(200);
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 修改用户信息，并保存到数据库
app.post("/updataUser", (req, res) => {
  let openid = req.query.openid;
  let nickname = req.query.nickname;
  let user_img = req.query.user_img;
  let sql = `UPDATE user SET nickname = '${nickname}', user_img = '${user_img}' WHERE openid ='${openid}';`;
  pool.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query(sql, function (error, results, fields) {
      connection.release();
      console.dir(req);
      res.header("Access-Control-Allow-Origin", "*");
      res.header("Access-Control-Allow-Headers", "content-type");
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options") res.send(200);
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 查询图片接口
app.post("/getImga", upload.single("image"), (req, res) => {
  //点击详情，加载了多少长度就查询多少
  let sql = `SELECT * FROM cs  WHERE twonum=${req.query.bbb} and onenum=${req.query.onenum} and state=2 LIMIT ${req.query.cx}`;
  // let sql = `SELECT * FROM cs WHERE twonum=1 LIMIT ${req.query.cx} `;
  pool.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query(sql, function (error, results, fields) {
      connection.release();
      console.dir(req);
      res.header("Access-Control-Allow-Origin", "*");
      res.header("Access-Control-Allow-Headers", "content-type");
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options") res.send(200);
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 查询一级分类的列
app.get("/onesort", upload.single("image"), (req, res) => {
  //点击详情，加载了多少长度就查询多少
  let sql = `select * from onesort`;
  pool.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query(sql, function (error, results, fields) {
      connection.release();
      console.dir(req);
      res.header("Access-Control-Allow-Origin", "*");
      res.header("Access-Control-Allow-Headers", "content-type");
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options") res.send(200);
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 查询二级分类的列
app.get("/twosort", upload.single("image"), (req, res) => {
  //点击详情，加载了多少长度就查询多少
  let num = req.query.num;
  let sql = `select * from twosort where onenum=${num}`;
  pool.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query(sql, function (error, results, fields) {
      connection.release();
      console.dir(req);
      res.header("Access-Control-Allow-Origin", "*");
      res.header("Access-Control-Allow-Headers", "content-type");
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options") res.send(200);
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
app.post("/getImg", upload.single("image"), (req, res) => {
  //分页查询，一次查询50张图片，触底刷新后返回参数再获取50张
  // let sql = `SELECT * FROM cs WHERE twonum=${req.query.twonum} LIMIT 5 OFFSET ${req.query.cx}`;
  let sql = `SELECT * FROM cs WHERE twonum=${req.query.twonum} and onenum=${req.query.onenum} and state=2  LIMIT 50 OFFSET ${req.query.cx}`;
  // let sql = `SELECT * FROM cs LIMIT 50 OFFSET ${req.query.cx}`;
  pool.getConnection(function (err, connection) {
    if (err) throw err;
    connection.query(sql, function (error, results, fields) {
      connection.release();
      console.dir(req);
      res.header("Access-Control-Allow-Origin", "*");
      res.header("Access-Control-Allow-Headers", "content-type");
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options") res.send(200);
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 上传图片接口
app.post("/upload", upload.single("image"), (req, res) => {
  // 处理上传的文件
  const file = req.file;
  let onenum = req.query.onenum;
  let twonum = req.query.twonum;
  let openid = req.query.openid;
  let img_number = req.query.img_number;
  let state = req.query.state;
  let release_time = req.query.release_time;
  // console.log(req.file.filename);
  if (!file) {
    // 如果没有上传文件，返回错误响应
    return res.status(400).send("No file uploaded");
  }
  // 上传成功，把访问的图片路径传入到数据库
  // let sql = `INSERT INTO cs (url, onenum, twonum) VALUES ('${req.file.filename}', ${onenum}, ${twonum});`;
  let sql = `INSERT INTO cs (url, onenum, twonum,openid,img_number,state,release_time) VALUES ('${req.file.filename}', '${onenum}', '${twonum}', '${openid}', '${img_number}',' ${state}', '${release_time}');`;
  pool.getConnection(function (err, connection) {
    if (err) throw err; // not connected!
    // Use the connection
    connection.query(sql, function (error, results, fields) {
      // When done with the connection, release it.
      connection.release();
      console.dir(req);
      //设置允许跨域的域名，*代表允许任意域名跨域
      res.header("Access-Control-Allow-Origin", "*");
      //允许的header类型
      res.header("Access-Control-Allow-Headers", "content-type");
      //跨域允许的请求方式
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options")
        res.send(200); //让options尝试请求快速结束
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });

  // 获取分类
  app.get("/sort", (req, res) => {
    let sql = "select * from user";
    pool.getConnection(function (err, connection) {
      if (err) throw err;
      connection.query(sql, function (error, results, fields) {
        connection.release();
        console.dir(req);
        res.header("Access-Control-Allow-Origin", "*");
        res.header("Access-Control-Allow-Headers", "content-type");
        res.header(
          "Access-Control-Allow-Methods",
          "DELETE,PUT,POST,GET,OPTIONS"
        );
        if (req.method.toLowerCase() == "options") res.send(200);
        else
          res.send({
            state: "success",
            list: results,
          });
        if (error) throw error;
      });
    });
  });
  // 文件上传成功，返回成功响应和文件路径
  // res.json({
  //   message: "File uploaded successfully",
  //   filePath: file.path,
  // });
});
// 小程序上每下载一次，下载记录+1，并记录下载者和下载的图片url
app.post("/api/xg", (req, res) => {
  let id = req.query.id;
  let state = req.query.state;
  let time = req.query.time;
  // 多个id则用,逗号分隔开
  let sql = `UPDATE cs    SET state =  ${state}  , examine='${time}'  WHERE id IN (${id});`;
  // let sql = `UPDATE cs SET state = ${state} WHERE id = ${id};`;
  pool.getConnection(function (err, connection) {
    if (err) throw err; // not connected!
    // Use the connection
    connection.query(sql, function (error, results, fields) {
      // When done with the connection, release it.
      connection.release();
      console.dir(req);
      //设置允许跨域的域名，*代表允许任意域名跨域
      res.header("Access-Control-Allow-Origin", "*");
      //允许的header类型
      res.header("Access-Control-Allow-Headers", "content-type");
      //跨域允许的请求方式
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options")
        res.send(200); //让options尝试请求快速结束
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});

app.get("/proxy", function (req, res) {
  const url = req.query.url;

  request(
    {
      url: url,
      timeout: 5000, // 设置超时时间
    },
    function (error, response, body) {
      if (error || response.statusCode !== 200) {
        res.status(500).send("Error proxying request");
      } else {
        res.send(body);
      }
    }
  );
});
// 去水印接口
app.post("/removeWatermark", (req, res) => {
  let inputUrl = req.query.inputUrl;

  if (!inputUrl) {
    return res.status(400).send("Missing input video URL");
  }

  // 从URL下载视频文件
  let downloadStream = require("download");
  download(inputUrl).pipe(fs.createWriteStream("input.mp4"));

  // 使用FFmpeg去除水印
  let command = ffmpeg("input.mp4")
    .input("watermark.png")
    .outputOptions(
      '-vf "movie=watermark.png[watermark];[in][watermark]overlay=W-w-10:H-h-10[out]"'
    )
    .output("output.mp4")
    .on("end", () => {
      // 返回没有水印的短视频下载链接
      res.send("http://example.com/output.mp4");
    })
    .on("error", (err) => {
      res.status(500).send(err.message);
    });

  command.run();
});
// 分割线······················································下面是后台管理系统用的接口
// 获取图片
app.get("/api/getImg", (req, res) => {
  // let sql = "select * from cs";
  // let sql = `SELECT cs.id, CS.url, cs.onenum, Cs.twonum, cs.downloadnum, Cs.openid, Cs.img_number, Cs.state, Cs.release_time, Cs.examine, onesort.Fname, onesort.sortnumber, twosort.twoName, twosort.twoNum, user.openid, user.nickname, user.user_img, user.zctime FROM cs INNER JOIN onesort ON cs.onenum = onesort.sortnumber INNER Join twosort ON cs.twonum = twosort.twoNum INNER Join user ON Cs.openid = user.openid`;
  let sql = `SELECT cs.id, Cs.url, Cs.onenum, Cs.twonum, Cs.downloadnum, Cs.openid, Cs.img_number, Cs.state, Cs.release_time, Cs.examine, onesort.Fname, onesort.sortnumber, twosort.twoName, twosort.twoNum, user.openid, user.nickname, user.user_img, user.zctime   
  FROM cs   
  INNER JOIN onesort ON Cs.onenum = onesort.sortnumber   
  INNER JOIN twosort ON Cs.twonum = twosort.twoNum   
  INNER JOIN user ON Cs.openid = user.openid   
  ORDER BY cs.id DESC`;
  pool.getConnection(function (err, connection) {
    if (err) throw err; // not connected!
    // Use the connection
    connection.query(sql, function (error, results, fields) {
      // When done with the connection, release it.
      connection.release();
      console.dir(req);
      //设置允许跨域的域名，*代表允许任意域名跨域
      res.header("Access-Control-Allow-Origin", "*");
      //允许的header类型
      res.header("Access-Control-Allow-Headers", "content-type");
      //跨域允许的请求方式
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options")
        res.send(200); //让options尝试请求快速结束
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 可以修改单个和多个的
app.post("/api/xg", (req, res) => {
  let id = req.query.id;
  let state = req.query.state;
  let time = req.query.time;
  // 多个id则用,逗号分隔开
  let sql = `UPDATE cs    SET state =  ${state}  , examine='${time}'  WHERE id IN (${id});`;
  // let sql = `UPDATE cs SET state = ${state} WHERE id = ${id};`;
  pool.getConnection(function (err, connection) {
    if (err) throw err; // not connected!
    // Use the connection
    connection.query(sql, function (error, results, fields) {
      // When done with the connection, release it.
      connection.release();
      console.dir(req);
      //设置允许跨域的域名，*代表允许任意域名跨域
      res.header("Access-Control-Allow-Origin", "*");
      //允许的header类型
      res.header("Access-Control-Allow-Headers", "content-type");
      //跨域允许的请求方式
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options")
        res.send(200); //让options尝试请求快速结束
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
// 删除多个和单个
app.post("/api/delete", (req, res) => {
  let id = req.query.id;
  // 多个id则用,逗号分隔开
  let sql = `DELETE FROM cs WHERE id IN (${id});  `;
  // let sql = `UPDATE cs SET state = ${state} WHERE id = ${id};`;
  pool.getConnection(function (err, connection) {
    if (err) throw err; // not connected!
    // Use the connection
    connection.query(sql, function (error, results, fields) {
      // When done with the connection, release it.
      connection.release();
      console.dir(req);
      //设置允许跨域的域名，*代表允许任意域名跨域
      res.header("Access-Control-Allow-Origin", "*");
      //允许的header类型
      res.header("Access-Control-Allow-Headers", "content-type");
      //跨域允许的请求方式
      res.header("Access-Control-Allow-Methods", "DELETE,PUT,POST,GET,OPTIONS");
      if (req.method.toLowerCase() == "options")
        res.send(200); //让options尝试请求快速结束
      else
        res.send({
          state: "success",
          list: results,
        });
      if (error) throw error;
    });
  });
});
app.use(express.static("uploads"));

app.listen(3000, () => {
  console.log("Server is running on port 3000");
});
